﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;

namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportReport2 : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_风险部帐龄表.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "帐龄";
            }

        }

        //收款条件
        private string _XiaoShouLeiXing = "";
        protected string XiaoShouLeiXing
        {
            get
            {
                if (_XiaoShouLeiXing == "")
                    _XiaoShouLeiXing = GetRequestValue<string>("XiaoShouLeiXing", "");
                return _XiaoShouLeiXing;
            }
            set
            {
                _XiaoShouLeiXing = value;
            }
        }
        protected string JingXiaoShang
        {
            get
            {
                return GetRequestValue<string>("JingXiaoShang", "");

            }
        }

        protected string GetSQLWhere()
        {
            //营业经理权限 2014-02-21
            string agentList = base.FTradeAgentPowerSqlWhere(UserId);
            string wheresql = string.IsNullOrEmpty(agentList) ? " 1=1 " : string.Format(" JingXiaoShang in ({0})", agentList);

            string sqlcommonFiled = "";
            if (!string.IsNullOrEmpty(JingXiaoShang))
            {
                sqlcommonFiled = QueryOperatorsEnumHelper.ConvertCommonOperator("JingXiaoShang", QueryOperatorsEnum.包含, JingXiaoShang);

                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + sqlcommonFiled;
                }
                else
                {
                    wheresql = sqlcommonFiled;
                }

            }




            if (XiaoShouLeiXing != "")
            {
                string[] xslxs = XiaoShouLeiXing.Split(new char[] { ',' });
                string xslxsql = " XiaoShouLeiXing in (''";
                foreach (string itm in xslxs)
                {
                    if (!string.IsNullOrEmpty(itm))
                    {
                        xslxsql += string.Format(",'{0}'", itm);
                    }
                }
                xslxsql += ")";

                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + xslxsql;
                }
                else
                {
                    wheresql = xslxsql;
                }
            }

            return wheresql;
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            
           // return;
            //初始化
            InitializeWorkbook();
            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            DataTable exportDT = FTradeReportController.Report2GetReportDate(GetSQLWhere())[0];
            int rowindexBase = 3;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {


                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["JingXiaoShang"].ToString());//经销商
                row1.CreateCell(1).SetCellValue(GetDBDouble(dr["WeiShouKuanXiang"]));//未支付金额
                row1.CreateCell(2).SetCellValue(GetDBDouble(dr["WeiDaoQiKuanXing"]));//未到期金额
                row1.CreateCell(3).SetCellValue(GetDBDouble(dr["Day30"]));
                row1.CreateCell(4).SetCellValue(GetDBDouble(dr["Day60"]));
                row1.CreateCell(5).SetCellValue(GetDBDouble(dr["Day90"]));
                row1.CreateCell(6).SetCellValue(GetDBDouble(dr["Day120"]));
                row1.CreateCell(7).SetCellValue(GetDBDouble(dr["Day150"]));
                row1.CreateCell(8).SetCellValue(GetDBDouble(dr["Day180"]));
                row1.CreateCell(9).SetCellValue(GetDBDouble(dr["Day210"]));
                row1.CreateCell(10).SetCellValue(GetDBDouble(dr["Day240"]));
                row1.CreateCell(11).SetCellValue(GetDBDouble(dr["Day270"]));
                row1.CreateCell(12).SetCellValue(GetDBDouble(dr["Day300"]));
                row1.CreateCell(13).SetCellValue(GetDBDouble(dr["Day330"]));
                row1.CreateCell(14).SetCellValue(GetDBDouble(dr["Day360"]));
                row1.CreateCell(15).SetCellValue(GetDBDouble(dr["Day720"]));
                row1.CreateCell(16).SetCellValue(GetDBDouble(dr["Day1080"]));
                row1.CreateCell(17).SetCellValue(GetDBDouble(dr["Day1080More"]));
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            HSSFRow rowsum = (HSSFRow)sheet1.CreateRow(rowReal);
            // row1.CreateCell(0).SetCellValue(1);
            rowsum.CreateCell(0).SetCellValue("合计：");//经销商
            rowsum.CreateCell(1).SetCellFormula(string.Format("sum(B4:B{0})", rowReal));//未支付金额
            rowsum.CreateCell(2).SetCellFormula(string.Format("sum(C4:C{0})", rowReal));//未到期金额
            rowsum.CreateCell(3).SetCellFormula(string.Format("sum(D4:D{0})", rowReal));
            rowsum.CreateCell(4).SetCellFormula(string.Format("sum(E4:E{0})", rowReal));
            rowsum.CreateCell(5).SetCellFormula(string.Format("sum(F4:F{0})", rowReal));
            rowsum.CreateCell(6).SetCellFormula(string.Format("sum(G4:G{0})", rowReal));
            rowsum.CreateCell(7).SetCellFormula(string.Format("sum(H4:H{0})", rowReal));
            rowsum.CreateCell(8).SetCellFormula(string.Format("sum(I4:I{0})", rowReal));
            rowsum.CreateCell(9).SetCellFormula(string.Format("sum(J4:J{0})", rowReal));
            rowsum.CreateCell(10).SetCellFormula(string.Format("sum(K4:K{0})", rowReal));
            rowsum.CreateCell(11).SetCellFormula(string.Format("sum(L4:L{0})", rowReal));
            rowsum.CreateCell(12).SetCellFormula(string.Format("sum(M4:M{0})", rowReal));
            rowsum.CreateCell(13).SetCellFormula(string.Format("sum(N4:N{0})", rowReal));
            rowsum.CreateCell(14).SetCellFormula(string.Format("sum(O4:O{0})", rowReal));
            rowsum.CreateCell(15).SetCellFormula(string.Format("sum(P4:P{0})", rowReal));
            rowsum.CreateCell(16).SetCellFormula(string.Format("sum(Q4:Q{0})", rowReal));
            rowsum.CreateCell(17).SetCellFormula(string.Format("sum(R4:R{0})", rowReal));

            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            //写入
            WriteToFile();
        }

        private Double GetDBDouble(object v)
        {
            if (v is DBNull)
                return 0;
            return Convert.ToDouble(v);
        }
        private string GetDBString(object v)
        {
            if (v is DBNull)
                return "";
            return v.ToString();
        }
        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls", "应收帐款帐龄表", DateTime.Now.ToString("yyyy-MM-dd")).Trim();
        }
    }
}